package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.RadeData;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.respons.RadeDataRs;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 各项率数据 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2025-04-29
 */
public interface RadeDataMapper extends BaseMapper<RadeData> {

    /**
     * 教师业绩（个人数据）
     */
    @Select({"<script>",
            "SELECT",
            "school_id,",
            "school_name,",
            "teacher_id,",
            "teacher_name,",
            "GROUP_CONCAT( lesson_num ) AS lesson_num,",
            "<when test='dataType == 2'>",
                "GROUP_CONCAT( year_class_id ) AS year_class_id,",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; and dataType == 1'>",
                "year_class_id,",
            "</when>",
            "fiducial_set_id,",
            "exam_num,",
            "projects_name,",
            "subjects_id,",
            "subjects_name,",
            "GROUP_CONCAT( fiducial_rade_num ) as fiducial_rade_num,",
            "GROUP_CONCAT( rade_num ) AS rade_num,",
            "GROUP_CONCAT( rank_type ) AS rank_type ",
            "FROM",
            "(",
                //"<foreach collection='lessonNumList' item='item' open='' separator='UNION all' close=''>",
                "SELECT",
                "school_id,",
                "getSchoolName ( school_id ) AS school_name,",
                "teacher_id,",
                "teacher_name,",
                "lesson_num,",
                "exam_num,",
                "fiducial_set_id,",
                "projects_name,",
                "subjects_id,",
                "year_class_id,",
                "getSubjectsName ( subjects_id ) AS subjects_name,",
                "ROUND(( rade_num * 100 ), 2 ) AS rade_num,",
                "fiducial_rade_num,",
                "rade_molecule_all,",
                "rade_denominator_all,",
                "(",
                "CASE ",
                "WHEN rank_desc = 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '1' ",
                "WHEN rank_desc &lt;&gt; 1 AND rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '2'",
//                "WHEN rank_desc &lt;&gt; 1 AND rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &lt; fiducial_rade_num THEN '3' ",
                "WHEN rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &lt; fiducial_rade_num THEN '3' ",
                "WHEN rank_asc = 1 THEN '4' ",
                "ELSE '' END ",
                ") AS rank_type ",
                "FROM",
                "(",
                    "SELECT",
                    "*,",
                    "<when test='dataType == 1'>",
                        "DENSE_RANK() OVER ( PARTITION BY lesson_num ORDER BY rade_num ASC ) AS rank_asc,",
                        "DENSE_RANK() OVER ( PARTITION BY lesson_num ORDER BY rade_num DESC ) AS rank_desc ",
                    "</when>",
                    "<when test='dataType == 2'>",
                        "DENSE_RANK() OVER ( PARTITION BY year_class_id ORDER BY rade_num ASC ) AS rank_asc,",
                        "DENSE_RANK() OVER ( PARTITION BY year_class_id ORDER BY rade_num DESC ) AS rank_desc ",
                    "</when>",
                    "FROM",
                    "(",
                        "SELECT",
                        "*,",
                        "SUM( rade_molecule ) AS rade_molecule_all,",
                        "SUM( rade_denominator ) AS rade_denominator_all,",
                        "<when test='dataType == 1'>",
                            "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) AS rade_num ",
                        "</when>",
                        "<when test='dataType == 2'>",
                            "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) + IFNULL( SUM( rade_molecule02 ) / SUM( rade_denominator02 ), 0 ) AS rade_num ",
                        "</when>",
                        "FROM",
                        "(",
                            "SELECT",
                            "IF( t4.pid = 0, t4.school_id, t4.pid ) AS school_id,",
                            "t2.teacher_id,",
                            "t5.`name` AS teacher_name,",
                            "t2.projects_id,",
                            "t2.lesson_num,",
                            "t2.subjects_id,",
                            "t2.year_class_id,",
                            "t1.projects_name,",
                            "t2.exam_num,",
                            "t2.rade_molecule,",
                            "t2.rade_denominator,",
                            "t2.rade_molecule02,",
                            "t2.rade_denominator02,",
                            "t3.rade_num AS fiducial_rade_num,",
                            "t3.fiducial_set_id ",
                            "FROM",
                            "rade_fiducial_set AS t1",
                            "INNER JOIN rade_fiducial_add AS t3 ON t1.id = t3.fiducial_set_id ",
                            "INNER JOIN rade_data AS t2 ON t1.id = t2.fiducial_set_id ",
                            "<when test='dataType == 1'>",
                                "AND t2.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum}  ",
                            "</when>",
                            "AND t2.subjects_id &lt;&gt; 0",
                            "INNER JOIN school_quarter AS t4 ON t1.year_part = t4.year_part ",
                            "AND t1.`quarter_num` = t4.`quarter_num` ",
                            "AND t2.school_id = t4.school_id",
                            "INNER JOIN staff AS t5 ON t2.teacher_id = t5.id ",
                            "WHERE",
                            "t2.deleted = 0 ",
                            "AND t1.`status` = 1",
                            "AND t2.stat_type = 2",
                            "AND t2.year_part = ${yearPart}",
                            "AND t2.quarter_num = ${quarterNum} ",
                            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                                "AND t2.subjects_id IN ( ${subjectsId} ) ",
                            "</when>",
                            "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                                "AND t1.id IN ( ${fiducialSetId} ) ",
                            "</when>",
                        ") AS t001 ",
                        "WHERE 1=1",
                        "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                            "AND school_id IN ( ${schoolId} ) ",
                        "</when>",
                        "GROUP BY",
                        "school_id,",
                        "teacher_id,",
                        "fiducial_set_id,",
                        "lesson_num ",
                        "<when test='dataType == 2'>",
                            ",year_class_id,exam_num",
                        "</when>",
                        "<when test='yearClassId!=null and yearClassId!=&apos;&apos; and dataType == 1'>",
                            ",year_class_id",
                        "</when>",
                    ") AS t01 ",
                ") AS t",
                //"</foreach>",
            ") AS tt ",
            "WHERE 1=1 ",
            "<when test='teacherName!=null and teacherName!=&apos;&apos;'>",
                "AND teacher_name LIKE '%${teacherName}%' ",
            "</when>",
            "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                "AND fiducial_set_id IN ( ${fiducialSetId} ) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                "AND year_class_id IN ( ${yearClassId} ) ",
            "</when>",
            "<when test='examNum!=null and examNum!=&apos;&apos;'>",
                "AND exam_num IN ( ${examNum} ) ",
            "</when>",
            "GROUP BY",
            "teacher_id,",
            "fiducial_set_id,",
            "school_id",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; and dataType == 1'>",
                ",year_class_id",
            "</when>",
            "<when test='dataType == 2'>",
                ",exam_num",
            "</when>",
            "</script>"})
    IPage<RadeDataRs> getRateByTeacherList(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") String schoolId, @Param("teacherName") String teacherName, @Param("fiducialSetId") String fiducialSetId, @Param("subjectsId") String subjectsId, @Param("yearClassId") String yearClassId, @Param("lessonNumList") List<Integer> lessonNumList, @Param("startLessonNum") Integer startLessonNum, @Param("endLessonNum") Integer endLessonNum, @Param("export") Integer export, @Param("examNum") String examNum, @Param("dataType") Integer dataType);


    /**
     * 年级负责人业绩（管理数据）
     */
    @Select({"<script>",
            "WITH tb01 AS (",
                "SELECT",
                "t1.year_part,",
                "t1.quarter_num,",
                "t1.school_id,",
                "t1.department_id,",
                "t1.staff_id AS teacher_id,",
                "t3.fiducial_set_id,",
                "t4.subjects_id,",
                "t4.year_class_id ",
                "FROM",
                "organization_department_principal AS t1",
                "INNER JOIN org_duty AS t2 ON t1.duty_id = t2.id",
                "INNER JOIN rade_look_set t3 ON t1.department_id = t3.department_id",
                "INNER JOIN org_principal_content AS t4 ON t1.id = t4.principal_id ",
                "WHERE",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum} ",
                "AND t1.duty_id = 2 ",
            ")",

            "SELECT",
            "school_id,",
            "school_name,",
            "teacher_id,",
            "principal_teacher_id,",
            "teacher_name,",
            "<when test='dataType == 1'>",
                "GROUP_CONCAT( lesson_num ) AS lesson_num,",
                "year_class_id,",
            "</when>",
            "<when test='dataType == 2'>",
                "GROUP_CONCAT( year_class_id ) AS year_class_id,",
            "</when>",
            "fiducial_set_id,",
            "projects_name,",
            "exam_num,",
            "subjects_id,",
            "subjects_name,",
            "GROUP_CONCAT( fiducial_rade_num ) as fiducial_rade_num,",
            "GROUP_CONCAT( rade_num ) AS rade_num,",
            "GROUP_CONCAT( rank_type ) AS rank_type ",
            "FROM",
            "(",
                //"<foreach collection='lessonNumList' item='item' open='' separator='UNION all' close=''>",
                "SELECT",
                "school_id,",
                "getSchoolName ( school_id ) AS school_name,",
                "teacher_id,",
                "principal_teacher_id,",
                "teacher_name,",
                "lesson_num,",
                "fiducial_set_id,",
                "projects_name,",
                "exam_num,",
                "subjects_id,",
                "year_class_id,",
                "getSubjectsName ( subjects_id ) AS subjects_name,",
                "ROUND(( rade_num * 100 ), 2 ) AS rade_num,",
                "fiducial_rade_num,",
                "rade_molecule_all,",
                "rade_denominator_all,",
                "(",
                "CASE ",
                "WHEN rank_desc = 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '1' ",
                "WHEN rank_desc &lt;&gt; 1 AND rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '2'",
//                "WHEN rank_desc &lt;&gt; 1 AND rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &lt; fiducial_rade_num THEN '3' ",
                "WHEN rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &lt; fiducial_rade_num THEN '3' ",
                "WHEN rank_asc = 1 THEN '4' ",
                "ELSE '' END ",
                ") AS rank_type ",
                "FROM",
                "(",
                    "SELECT",
                    "*,",
                    "DENSE_RANK() OVER ( PARTITION BY lesson_num,year_class_id ORDER BY rade_num ASC ) AS rank_asc,",
                    "DENSE_RANK() OVER ( PARTITION BY lesson_num,year_class_id ORDER BY rade_num DESC ) AS rank_desc ",
                    "FROM",
                    "(",
                        "SELECT",
                        "t001.*,",
                        "tb01.teacher_id AS principal_teacher_id,",
                        "t002.`name` AS teacher_name,",
                        "SUM( rade_molecule ) AS rade_molecule_all,",
                        "SUM( rade_denominator ) AS rade_denominator_all,",
                        "<when test='dataType == 1'>",
                            "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) AS rade_num ",
                        "</when>",
                        "<when test='dataType == 2'>",
                            "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) + IFNULL( SUM( rade_molecule02 ) / SUM( rade_denominator02 ), 0 ) AS rade_num ",
                        "</when>",
                        "FROM",
                        "(",
                            "SELECT",
                            "IF ( t4.pid = 0, t4.school_id, t4.pid ) AS school_id,",
                            "t2.teacher_id,",
                            "t2.projects_id,",
                            "t2.lesson_num,",
                            "t2.subjects_id,",
                            "t2.exam_num,",
                            "t1.projects_name,",
                            "t2.rade_molecule,",
                            "t2.rade_denominator,",
                            "t2.rade_molecule02,",
                            "t2.rade_denominator02,",
                            "t3.rade_num AS fiducial_rade_num,",
                            "t3.fiducial_set_id,",
                            "t2.year_class_id",
                            "FROM",
                            "rade_fiducial_set AS t1",
                            "INNER JOIN rade_fiducial_add AS t3 ON t1.id = t3.fiducial_set_id ",
                            "INNER JOIN rade_data AS t2 ON t1.id = t2.fiducial_set_id ",
                            "<when test='dataType == 1'>",
                                "AND t2.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum}",
                                "AND t2.subjects_id &lt;&gt; 0",
                            "</when>",
                            "<when test='dataType == 2'>",
                                "AND t2.subjects_id = t3.subjects_id",
                                "AND t2.year_class_id = t3.year_class_id ",
                            "</when>",
                            "INNER JOIN school_quarter AS t4 ON t1.year_part = t4.year_part ",
                            "AND t1.`quarter_num` = t4.`quarter_num` ",
                            "AND t2.school_id = t4.school_id ",
                            "WHERE",
                            "t2.deleted = 0 ",
                            "AND t1.`status` = 1",
                            "AND t2.stat_type = 2",
                            "AND t2.year_part = ${yearPart}",
                            "AND t2.quarter_num = ${quarterNum} ",
                            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                                "AND t2.subjects_id IN ( ${subjectsId} ) ",
                            "</when>",
                            "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                                "AND t1.id IN ( ${fiducialSetId} ) ",
                            "</when>",
                        ") AS t001 ",
                        "INNER JOIN tb01 ON tb01.fiducial_set_id = t001.fiducial_set_id ",
                        "<when test='dataType == 1'>",
                            "AND tb01.subjects_id = t001.subjects_id ",
                        "</when>",
                        "AND tb01.year_class_id = t001.year_class_id ",
                        "AND tb01.school_id = t001.school_id",
                        "INNER JOIN staff AS t002 ON tb01.teacher_id = t002.id ",
                        "WHERE 1=1",
                        "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                            "AND t001.school_id IN ( ${schoolId} ) ",
                        "</when>",
                        "GROUP BY",
                        "school_id,",
                        "principal_teacher_id,",
                        "lesson_num, ",
                        "fiducial_set_id, ",
                        "year_class_id ",
                        "<when test='dataType == 2'>",
                            ",subjects_id,exam_num",
                        "</when>",
                    ") AS t01 ",
                    "GROUP BY",
                    "principal_teacher_id,",
                    "fiducial_set_id,",
                    "lesson_num,",
                    "year_class_id ",
                    "<when test='dataType == 2'>",
                        ",subjects_id,exam_num",
                    "</when>",
                ") AS t01 ",
            ") AS t ",
                //"</foreach>",
            "WHERE 1=1",
            "<when test='teacherName!=null and teacherName!=&apos;&apos;'>",
                "AND teacher_name LIKE '%${teacherName}%' ",
            "</when>",
            "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                "AND fiducial_set_id IN ( ${fiducialSetId} ) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                "AND year_class_id IN ( ${yearClassId} ) ",
            "</when>",
            "<when test='examNum!=null and examNum!=&apos;&apos;'>",
                "AND exam_num IN ( ${examNum} ) ",
            "</when>",
            "GROUP BY",
            "principal_teacher_id,",
            "fiducial_set_id",
            "<when test='dataType == 1'>",
                ",year_class_id",
            "</when>",
            "<when test='dataType == 2'>",
                ",subjects_id,exam_num",
            "</when>",
            "</script>"})
    IPage<RadeDataRs> getRateByYearClassTeacherList(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") String schoolId, @Param("teacherName") String teacherName, @Param("fiducialSetId") String fiducialSetId, @Param("subjectsId") String subjectsId, @Param("yearClassId") String yearClassId, @Param("lessonNumList") List<Integer> lessonNumList, @Param("startLessonNum") Integer startLessonNum, @Param("endLessonNum") Integer endLessonNum, @Param("export") Integer export, @Param("examNum") String examNum, @Param("dataType") Integer dataType);

    /**
     * 主管数据（管理数据）
     */
    @Select({"<script>",
            "WITH tb01 AS (",
                "SELECT",
                "t1.year_part,",
                "t1.quarter_num,",
                "t1.school_id,",
                "t1.department_id,",
                "t1.organization_id,",
                "t4.department_name,",
                "t1.staff_id AS teacher_id,",
                "t3.fiducial_set_id",
                "FROM",
                "organization_department_principal AS t1",
                "INNER JOIN org_duty AS t2 ON t1.duty_id = t2.id",
                "INNER JOIN rade_look_set t3 ON t1.department_id = t3.department_id",
                "LEFT JOIN organization_department AS t4 ON t4.id = t1.organization_id AND t4.school_id = t1.school_id ",
                "WHERE",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum} ",
                "AND t1.duty_id = 1 ",
                "AND t4.`status` = 1  ",
            ")",

            "SELECT",
            "school_id,",
            "school_name,",
            "teacher_id,",
            "principal_teacher_id,",
            "teacher_name,",
            "<when test='dataType == 1'>",
                "GROUP_CONCAT( lesson_num ) AS lesson_num,",
            "year_class_id,",
            "</when>",
            "<when test='dataType == 2'>",
                "GROUP_CONCAT( year_class_id ) AS year_class_id,",
            "</when>",
            "fiducial_set_id,",
            "projects_name,",
            "department_id,",
            "department_name,",
            "exam_num,",
            "subjects_id,",
            "subjects_name,",
            "GROUP_CONCAT( fiducial_rade_num ) as fiducial_rade_num,",
            "GROUP_CONCAT( rade_num ) AS rade_num,",
            "GROUP_CONCAT( rank_type ) AS rank_type ",
            "FROM",
            "(",
                //"<foreach collection='lessonNumList' item='item' open='' separator='UNION all' close=''>",
                "SELECT",
                "school_id,",
                "getSchoolName ( school_id ) AS school_name,",
                "teacher_id,",
                "principal_teacher_id,",
                "teacher_name,",
                "lesson_num,",
                "fiducial_set_id,",
                "projects_name,",
                "exam_num,",
                "subjects_id,",
                "year_class_id,",
                "department_id,",
                "department_name,",
                "getSubjectsName ( subjects_id ) AS subjects_name,",
                "ROUND(( rade_num * 100 ), 2 ) AS rade_num,",
                "fiducial_rade_num,",
                "rade_molecule_all,",
                "rade_denominator_all,",
                "(",
                "CASE ",
                "WHEN rank_desc = 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '1' ",
                "WHEN rank_desc &lt;&gt; 1 AND rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '2'",
    //                "WHEN rank_desc &lt;&gt; 1 AND rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &lt; fiducial_rade_num THEN '3' ",
                "WHEN rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &lt; fiducial_rade_num THEN '3' ",
                "WHEN rank_asc = 1 THEN '4' ",
                "ELSE '' END ",
                ") AS rank_type ",
                "FROM",
                "(",
                    "SELECT",
                    "*,",
                    "<when test='dataType == 1'>",
                        "DENSE_RANK() OVER ( PARTITION BY lesson_num ORDER BY rade_num ASC ) AS rank_asc,",
                        "DENSE_RANK() OVER ( PARTITION BY lesson_num ORDER BY rade_num DESC ) AS rank_desc ",
                    "</when>",
                    "<when test='dataType == 2'>",
                        "DENSE_RANK() OVER ( PARTITION BY year_class_id ORDER BY rade_num ASC ) AS rank_asc,",
                        "DENSE_RANK() OVER ( PARTITION BY year_class_id ORDER BY rade_num DESC ) AS rank_desc ",
                    "</when>",
                    "FROM",
                    "(",
                        "SELECT",
                        "t001.*,",
                        "tb01.teacher_id AS principal_teacher_id,",
                        "tb01.department_id,",
                        "tb01.department_name,",
                        "t002.`name` AS teacher_name,",
                        "SUM( rade_molecule ) AS rade_molecule_all,",
                        "SUM( rade_denominator ) AS rade_denominator_all,",
                        "<when test='dataType == 1'>",
                            "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) AS rade_num ",
                        "</when>",
                        "<when test='dataType == 2'>",
                            "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) + IFNULL( SUM( rade_molecule02 ) / SUM( rade_denominator02 ), 0 ) AS rade_num ",
                        "</when>",
                        "FROM",
                        "(",
                            "SELECT",
                            "IF ( t4.pid = 0, t4.school_id, t4.pid ) AS school_id,",
                            "t2.teacher_id,",
                            "t2.projects_id,",
                            "t2.lesson_num,",
                            "t2.subjects_id,",
                            "t2.exam_num,",
                            "t1.projects_name,",
                            "t2.rade_molecule,",
                            "t2.rade_denominator,",
                            "t2.rade_molecule02,",
                            "t2.rade_denominator02,",
                            "t3.rade_num AS fiducial_rade_num,",
                            "t3.fiducial_set_id,",
                            "t2.year_class_id",
                            "FROM",
                            "rade_fiducial_set AS t1",
                            "INNER JOIN rade_fiducial_add AS t3 ON t1.id = t3.fiducial_set_id ",
                            "INNER JOIN rade_data AS t2 ON t1.id = t2.fiducial_set_id ",
                            "<when test='dataType == 1'>",
                                "AND t2.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum}",
                                "AND t2.subjects_id &lt;&gt; 0",
                                "<when test='yearClassId==null or yearClassId==&apos;&apos;'>",
                                    "AND t2.year_class_id IS NULL ",
                                "</when>",
                            "</when>",
                            "<when test='dataType == 2'>",
                                "AND t2.subjects_id = t3.subjects_id",
                                "AND t2.year_class_id = t3.year_class_id ",
                            "</when>",
                            "INNER JOIN school_quarter AS t4 ON t1.year_part = t4.year_part ",
                            "AND t1.`quarter_num` = t4.`quarter_num` ",
                            "AND t2.school_id = t4.school_id ",
                            "WHERE",
                            "t2.deleted = 0 ",
                            "AND t1.`status` = 1",
                            "AND t2.stat_type = 6",
                            "AND t2.year_part = ${yearPart}",
                            "AND t2.quarter_num = ${quarterNum} ",
                            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                                "AND t2.year_class_id IN ( ${yearClassId} ) ",
                            "</when>",
                            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                                "AND t2.subjects_id IN ( ${subjectsId} ) ",
                            "</when>",
                            "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                                "AND t1.id IN ( ${fiducialSetId} ) ",
                            "</when>",
                        ") AS t001 ",
                        "INNER JOIN tb01 ON tb01.fiducial_set_id = t001.fiducial_set_id ",
                        "AND tb01.school_id = t001.school_id",
                        "INNER JOIN staff AS t002 ON tb01.teacher_id = t002.id ",
                        "WHERE 1=1",
                        "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                            "AND t001.school_id IN ( ${schoolId} ) ",
                        "</when>",
                        "GROUP BY",
                        "school_id,",
                        "principal_teacher_id,",
                        "lesson_num, ",
                        "fiducial_set_id ",
                        "<when test='dataType == 2'>",
                            ",year_class_id,subjects_id,exam_num",
                        "</when>",
                    ") AS t01 ",
                    "GROUP BY",
                    "principal_teacher_id,",
                    "fiducial_set_id,",
                    "lesson_num",
                    "<when test='dataType == 2'>",
                        ",year_class_id,subjects_id,exam_num",
                    "</when>",
                ") AS t01 ",
            ") AS t ",
            //"</foreach>",
            "WHERE 1=1",
            "<when test='teacherName!=null and teacherName!=&apos;&apos;'>",
                "AND teacher_name LIKE '%${teacherName}%' ",
            "</when>",
            "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                "AND fiducial_set_id IN ( ${fiducialSetId} ) ",
            "</when>",
            "<when test='examNum!=null and examNum!=&apos;&apos;'>",
                "AND exam_num IN ( ${examNum} ) ",
            "</when>",
            "<when test='departmentId!=null and departmentId!=&apos;&apos;'>",
                "AND department_id IN ( ${departmentId} ) ",
            "</when>",
            "GROUP BY",
            "principal_teacher_id,",
            "fiducial_set_id",
            "<when test='dataType == 2'>",
                ",subjects_id,exam_num",
            "</when>",
            "</script>"})
    IPage<RadeDataRs> getRateByChargeTeacherList(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") String schoolId, @Param("teacherName") String teacherName, @Param("fiducialSetId") String fiducialSetId, @Param("subjectsId") String subjectsId, @Param("yearClassId") String yearClassId, @Param("lessonNumList") List<Integer> lessonNumList, @Param("startLessonNum") Integer startLessonNum, @Param("endLessonNum") Integer endLessonNum, @Param("export") Integer export, @Param("departmentId") String departmentId, @Param("examNum") String examNum, @Param("dataType") Integer dataType);

    /**
     * 年级负责人业绩（个人数据）
     */
    @Select({"<script>",
            "WITH tb01 AS (",
                "SELECT",
                "t1.year_part,",
                "t1.quarter_num,",
                "t1.school_id,",
                "t1.department_id,",
                "t1.staff_id AS teacher_id ",
                "FROM",
                "organization_department_principal AS t1",
                "INNER JOIN org_duty AS t2 ON t1.duty_id = t2.id",
                "WHERE",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum} ",
                "AND t1.duty_id = 2 ",
            ")",

            "SELECT",
            "school_id,",
            "school_name,",
            "teacher_id,",
            "principal_teacher_id,",
            "teacher_name,",
            "<when test='dataType == 1'>",
                "GROUP_CONCAT( lesson_num ) AS lesson_num,",
                "year_class_id,",
            "</when>",
            "<when test='dataType == 2'>",
                "GROUP_CONCAT( year_class_id ) AS year_class_id,",
            "</when>",
            "fiducial_set_id,",
            "projects_name,",
            "exam_num,",
            "subjects_id,",
            "subjects_name,",
            "GROUP_CONCAT( fiducial_rade_num ) as fiducial_rade_num,",
            "GROUP_CONCAT( rade_num ) AS rade_num,",
            "GROUP_CONCAT( rank_type ) AS rank_type ",
            "FROM",
            "(",
                //"<foreach collection='lessonNumList' item='item' open='' separator='UNION all' close=''>",
                "SELECT",
                "school_id,",
                "getSchoolName ( school_id ) AS school_name,",
                "teacher_id,",
                "principal_teacher_id,",
                "teacher_name,",
                "lesson_num,",
                "fiducial_set_id,",
                "projects_name,",
                "exam_num,",
                "subjects_id,",
                "year_class_id,",
                "getSubjectsName ( subjects_id ) AS subjects_name,",
                "ROUND(( rade_num * 100 ), 2 ) AS rade_num,",
                "fiducial_rade_num,",
                "rade_molecule_all,",
                "rade_denominator_all,",
                "(",
                "CASE ",
                "WHEN rank_desc = 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '1' ",
                "WHEN rank_desc &lt;&gt; 1 AND rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '2'",
                "WHEN rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &lt; fiducial_rade_num THEN '3' ",
                "WHEN rank_asc = 1 THEN '4' ",
                "ELSE '' END ",
                ") AS rank_type ",
                "FROM",
                "(",
                    "SELECT",
                    "*,",
                    "<when test='dataType == 1'>",
                        "DENSE_RANK() OVER ( PARTITION BY lesson_num ORDER BY rade_num ASC ) AS rank_asc,",
                        "DENSE_RANK() OVER ( PARTITION BY lesson_num ORDER BY rade_num DESC ) AS rank_desc ",
                    "</when>",
                    "<when test='dataType == 2'>",
                        "DENSE_RANK() OVER ( PARTITION BY year_class_id ORDER BY rade_num ASC ) AS rank_asc,",
                        "DENSE_RANK() OVER ( PARTITION BY year_class_id ORDER BY rade_num DESC ) AS rank_desc ",
                    "</when>",
                    "FROM",
                    "(",
                    "SELECT",
                    "t001.*,",
                    "tb01.teacher_id AS principal_teacher_id,",
                    "t002.`name` AS teacher_name,",
                    "SUM( rade_molecule ) AS rade_molecule_all,",
                    "SUM( rade_denominator ) AS rade_denominator_all,",
                    "<when test='dataType == 1'>",
                        "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) AS rade_num ",
                    "</when>",
                    "<when test='dataType == 2'>",
                        "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) + IFNULL( SUM( rade_molecule02 ) / SUM( rade_denominator02 ), 0 ) AS rade_num ",
                    "</when>",
                    "FROM",
                    "(",
                        "SELECT",
                        "IF ( t4.pid = 0, t4.school_id, t4.pid ) AS school_id,",
                        "t2.teacher_id,",
                        "t2.projects_id,",
                        "t2.lesson_num,",
                        "t2.subjects_id,",
                        "t1.projects_name,",
                        "t2.exam_num,",
                        "t2.rade_molecule,",
                        "t2.rade_denominator,",
                        "t2.rade_molecule02,",
                        "t2.rade_denominator02,",
                        "t3.rade_num AS fiducial_rade_num,",
                        "t3.fiducial_set_id,",
                        "t2.year_class_id",
                        "FROM",
                        "rade_fiducial_set AS t1",
                        "INNER JOIN rade_fiducial_add AS t3 ON t1.id = t3.fiducial_set_id ",
                        "INNER JOIN rade_data AS t2 ON t1.id = t2.fiducial_set_id ",
                        "<when test='dataType == 1'>",
                            "AND t2.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum}",
                        "</when>",
                        "AND t2.subjects_id &lt;&gt; 0",
                        "INNER JOIN school_quarter AS t4 ON t1.year_part = t4.year_part ",
                        "AND t1.`quarter_num` = t4.`quarter_num` ",
                        "AND t2.school_id = t4.school_id ",
                        "WHERE",
                        "t2.deleted = 0 ",
                        "AND t1.`status` = 1",
                        "AND t2.stat_type = 2",
                        "AND t2.year_part = ${yearPart}",
                        "AND t2.quarter_num = ${quarterNum} ",
                        "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                            "AND t2.year_class_id IN ( ${yearClassId} ) ",
                        "</when>",
                        "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                            "AND t2.subjects_id IN ( ${subjectsId} ) ",
                        "</when>",
                        "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                            "AND t1.id IN ( ${fiducialSetId} ) ",
                        "</when>",
                    ") AS t001 ",
                    "INNER JOIN tb01 ON tb01.teacher_id = t001.teacher_id ",
                    "INNER JOIN staff AS t002 ON tb01.teacher_id = t002.id ",
                    "WHERE 1=1",
                    "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                        "AND t001.school_id IN ( ${schoolId} ) ",
                    "</when>",
                    "GROUP BY",
                    "school_id,",
                    "principal_teacher_id,",
                    "lesson_num, ",
                    "fiducial_set_id",
                    "<when test='dataType == 2'>",
                        ",year_class_id,subjects_id,exam_num",
                    "</when>",
                ") AS t01 ",
                "GROUP BY",
                "principal_teacher_id,",
                "fiducial_set_id,",
                "lesson_num",
                "<when test='dataType == 2'>",
                    ",year_class_id,exam_num",
                "</when>",
                ") AS t01 ",
            ") AS t ",
            //"</foreach>",
            "WHERE 1=1",
            "<when test='teacherName!=null and teacherName!=&apos;&apos;'>",
                "AND teacher_name LIKE '%${teacherName}%' ",
            "</when>",
            "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                "AND fiducial_set_id IN ( ${fiducialSetId} ) ",
            "</when>",
            "<when test='examNum!=null and examNum!=&apos;&apos;'>",
                "AND exam_num IN ( ${examNum} ) ",
            "</when>",
            "GROUP BY",
            "principal_teacher_id,",
            "fiducial_set_id",
            "<when test='dataType == 2'>",
                ",exam_num",
            "</when>",
            "</script>"})
    IPage<RadeDataRs> getRateByYearClassTeacherOwnList(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") String schoolId, @Param("teacherName") String teacherName, @Param("fiducialSetId") String fiducialSetId, @Param("subjectsId") String subjectsId, @Param("yearClassId") String yearClassId, @Param("lessonNumList") List<Integer> lessonNumList, @Param("startLessonNum") Integer startLessonNum, @Param("endLessonNum") Integer endLessonNum, @Param("export") Integer export, @Param("examNum") String examNum, @Param("dataType") Integer dataType);

    /**
     * 主管数据（个人数据）
     */
    @Select({"<script>",
            "WITH tb01 AS (",
            "SELECT",
            "t1.year_part,",
            "t1.quarter_num,",
            "t1.school_id,",
            "t1.department_id,",
            "t3.department_name,",
            "t1.staff_id AS teacher_id",
            "FROM",
            "organization_department_principal AS t1",
            "INNER JOIN org_duty AS t2 ON t1.duty_id = t2.id",
            "LEFT JOIN organization_department AS t3 ON t3.id = t1.organization_id AND t3.school_id = t1.school_id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.duty_id = 1 ",
            "AND t3.`status` = 1  ",
            ")",

            "SELECT",
            "school_id,",
            "school_name,",
            "teacher_id,",
            "principal_teacher_id,",
            "teacher_name,",
            "<when test='dataType == 1'>",
                "GROUP_CONCAT( lesson_num ) AS lesson_num,",
                "year_class_id,",
            "</when>",
            "<when test='dataType == 2'>",
                "GROUP_CONCAT( year_class_id ) AS year_class_id,",
            "</when>",
            "fiducial_set_id,",
            "projects_name,",
            "subjects_id,",
            "exam_num,",
            "department_id,",
            "department_name,",
            "subjects_name,",
            "GROUP_CONCAT( fiducial_rade_num ) as fiducial_rade_num,",
            "GROUP_CONCAT( rade_num ) AS rade_num,",
            "GROUP_CONCAT( rank_type ) AS rank_type ",
            "FROM",
            "(",
                //"<foreach collection='lessonNumList' item='item' open='' separator='UNION all' close=''>",
                "SELECT",
                "school_id,",
                "getSchoolName ( school_id ) AS school_name,",
                "teacher_id,",
                "principal_teacher_id,",
                "teacher_name,",
                "lesson_num,",
                "fiducial_set_id,",
                "department_id,",
                "department_name,",
                "projects_name,",
                "exam_num,",
                "subjects_id,",
                "year_class_id,",
                "getSubjectsName ( subjects_id ) AS subjects_name,",
                "ROUND(( rade_num * 100 ), 2 ) AS rade_num,",
                "fiducial_rade_num,",
                "rade_molecule_all,",
                "rade_denominator_all,",
                "(",
                "CASE ",
                "WHEN rank_desc = 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '1' ",
                "WHEN rank_desc &lt;&gt; 1 AND rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &gt;= fiducial_rade_num THEN '2'",
                "WHEN rank_asc &lt;&gt; 1 AND ROUND( rade_num * 100, 2 ) &lt; fiducial_rade_num THEN '3' ",
                "WHEN rank_asc = 1 THEN '4' ",
                "ELSE '' END ",
                ") AS rank_type ",
                "FROM",
                "(",
                    "SELECT",
                    "*,",
                    "<when test='dataType == 1'>",
                        "DENSE_RANK() OVER ( PARTITION BY lesson_num ORDER BY rade_num ASC ) AS rank_asc,",
                        "DENSE_RANK() OVER ( PARTITION BY lesson_num ORDER BY rade_num DESC ) AS rank_desc ",
                    "</when>",
                    "<when test='dataType == 2'>",
                        "DENSE_RANK() OVER ( PARTITION BY year_class_id ORDER BY rade_num ASC ) AS rank_asc,",
                        "DENSE_RANK() OVER ( PARTITION BY year_class_id ORDER BY rade_num DESC ) AS rank_desc ",
                    "</when>",
                    "FROM",
                    "(",
                        "SELECT",
                        "t001.*,",
                        "tb01.teacher_id AS principal_teacher_id,",
                        "tb01.department_id,",
                        "tb01.department_name,",
                        "t002.`name` AS teacher_name,",
                        "SUM( rade_molecule ) AS rade_molecule_all,",
                        "SUM( rade_denominator ) AS rade_denominator_all,",
                        "<when test='dataType == 1'>",
                            "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) AS rade_num ",
                        "</when>",
                        "<when test='dataType == 2'>",
                            "IFNULL( SUM( rade_molecule ) / SUM( rade_denominator ), 0 ) + IFNULL( SUM( rade_molecule02 ) / SUM( rade_denominator02 ), 0 ) AS rade_num ",
                        "</when>",
                        "FROM",
                        "(",
                        "SELECT",
                        "IF ( t4.pid = 0, t4.school_id, t4.pid ) AS school_id,",
                        "t2.teacher_id,",
                        "t2.projects_id,",
                        "t2.lesson_num,",
                        "t2.subjects_id,",
                        "t1.projects_name,",
                        "t2.exam_num,",
                        "t2.rade_molecule,",
                        "t2.rade_denominator,",
                        "t2.rade_molecule02,",
                        "t2.rade_denominator02,",
                        "t3.rade_num AS fiducial_rade_num,",
                        "t3.fiducial_set_id,",
                        "t2.year_class_id",
                        "FROM",
                        "rade_fiducial_set AS t1",
                        "INNER JOIN rade_fiducial_add AS t3 ON t1.id = t3.fiducial_set_id ",
                        "INNER JOIN rade_data AS t2 ON t1.id = t2.fiducial_set_id ",
                        "<when test='dataType == 1'>",
                            "AND t2.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum}",
                        "</when>",
                        "AND t2.subjects_id &lt;&gt; 0",
                        "INNER JOIN school_quarter AS t4 ON t1.year_part = t4.year_part ",
                        "AND t1.`quarter_num` = t4.`quarter_num` ",
                        "AND t2.school_id = t4.school_id ",
                        "WHERE",
                        "t2.deleted = 0 ",
                        "AND t1.`status` = 1",
                        "AND t2.stat_type = 2",
                        "AND t2.year_part = ${yearPart}",
                        "AND t2.quarter_num = ${quarterNum} ",
                        "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                            "AND t2.year_class_id IN ( ${yearClassId} ) ",
                        "</when>",
                        "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                            "AND t2.subjects_id IN ( ${subjectsId} ) ",
                        "</when>",
                        "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                            "AND t1.id IN ( ${fiducialSetId} ) ",
                        "</when>",
                    ") AS t001 ",
                    "INNER JOIN tb01 ON tb01.teacher_id = t001.teacher_id ",
                    "INNER JOIN staff AS t002 ON tb01.teacher_id = t002.id ",
                    "WHERE 1=1",
                    "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                        "AND t001.school_id IN ( ${schoolId} ) ",
                    "</when>",
                    "GROUP BY",
                    "school_id,",
                    "principal_teacher_id,",
                    "lesson_num, ",
                    "fiducial_set_id",
                    "<when test='dataType == 2'>",
                        ",year_class_id,subjects_id,exam_num",
                    "</when>",
                ") AS t01 ",
                "GROUP BY",
                "principal_teacher_id,",
                "fiducial_set_id,",
                "lesson_num",
                "<when test='dataType == 2'>",
                    ",year_class_id,subjects_id,exam_num",
                "</when>",
                ") AS t01 ",
            ") AS t ",
            //"</foreach>",
            "WHERE 1=1",
            "<when test='teacherName!=null and teacherName!=&apos;&apos;'>",
                "AND teacher_name LIKE '%${teacherName}%' ",
            "</when>",
            "<when test='fiducialSetId!=null and fiducialSetId!=&apos;&apos;'>",
                "AND fiducial_set_id IN ( ${fiducialSetId} ) ",
            "</when>",
            "<when test='examNum!=null and examNum!=&apos;&apos;'>",
                "AND exam_num IN ( ${examNum} ) ",
            "</when>",
            "<when test='departmentId!=null and departmentId!=&apos;&apos;'>",
                "AND department_id IN ( ${departmentId} ) ",
            "</when>",
            "GROUP BY",
            "principal_teacher_id,",
            "fiducial_set_id",
            "<when test='dataType == 2'>",
                ",subjects_id,exam_num",
            "</when>",
            "</script>"})
    IPage<RadeDataRs> getRateByChargeTeacherOwnList(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") String schoolId, @Param("teacherName") String teacherName, @Param("fiducialSetId") String fiducialSetId, @Param("subjectsId") String subjectsId, @Param("yearClassId") String yearClassId, @Param("lessonNumList") List<Integer> lessonNumList, @Param("startLessonNum") Integer startLessonNum, @Param("endLessonNum") Integer endLessonNum, @Param("export") Integer export, @Param("departmentId") String departmentId, @Param("examNum") String examNum, @Param("dataType") Integer dataType);


}
